- L3 MIASHS/Ingémath/METIS
- Université Paris Cité
- Année 2024-2025
- Course Homepage
- Moodle
Dans une métropole, un exploitant de vélos partagés cherche à archiver des informations concernant les vélos et les stations.
Chaque vélo est identifié par un numéro. Un vélo possède une date de mise en service, un type (électrique/mécanique). Un vélo possède éventuellement une date de retrait de service.
Une station est identifiée par un numéro. Une station possède un nom. Une station possède une position (lattitude, longitude) et une altitude. Une station possède un nombre de fixe de bornes d’accrochages.
Un trajet est effectué par un vélo entre un instant (timestamp) de départ et un instant d’arrivée. Un trajet part d’une station et arrive à une station (pas toujours différente).
Proposer un diagrammme entité-association correspondant à cette modélisation.
trajet est une association ternaire avec des cardinalités 0:n. C’est une association plusieurs-plusieurs.
Si besoin, transformez votre schéme de façon à ce qu’il ne comporte plus d’associations d’arité supérieure ou égale à 3, ou d’associations plusieurs-plusieurs (0:n pour plusieurs entités participantes).
Proposer une traduction en pattes de corbeau du diagramme EA proposé en réponse
à la première question.
erDiagram
TRAJET }o--|| VELO : velo_num
TRAJET }o--|| STATION : origin_num
TRAJET }o--|| STATION : dest_num
VELO {
integer num PK
text type
date mise_service_date
date retrait-date
}
STATION {
integer num PK
numeric lat
numeric long
text name
integer docks
}
TRAJET {
integer velo_num PK, FK
integer origin_num PK, FK
integer dest_num FK
timestamp start PK
timestamp end
}
Explicitez d’éventuelles contraintes externes (si vous l’avez déjà fait en marge du diagramme entité-association, contentez-vous d’un renvoi).
- La date de retrait de service d’un vélo ne peut pas précéder sa mise en service (
CHECK) - Un vélo ne peut pas être utilisé pour un trajet s’il nest pas en service (contrainte multitable, réalisable avec une fonction qui renvoie l’intervalle pendant lequel un vélo est en service et une contrainte
CHECKqui vérifie que(start,end )est inclus dans l’intervalle de service) - Le début d’un trajet doit précéder sa fin (
CHECK) - Un même vélo ne peut pas effectuer deux trajets simultanément (
EXCLUDE)
Dans la suite, vous formulerez les requêtes dans le schéma relationnel défini par votre schéma en pattes de corbeau.
Pour chaque station, chaque heure de la journée, comptez le nombre de trajets partis de la station.
PostgreSQL propose un type timestamp (qui permet de représenter les instants à la microseconde près). PostgreSQL propose une fonction fonction date_part(text, timestamp) qui permet d’extraire les différents composants d’un objet de type timestamp. date_part('month', timestamp) extrait le mois grégorien (un entier de 1 à 12), date_part('hour', timestamp) extrait l’heure du jour (un entier de 0 à 23).
SELECT
tr.origin_num, date_part('hour', start) AS heure,
count(*) AS n_trajet
FROM
trajet tr
GROUP BY
tr.origin_num, date_part('hour', start) ;Pour chaque vélo, comptez le nombre de trajets de plus d’une heure effectués.
En PostgreSQL, la différence de deux objets de type timestamp est un objet de type interval (durée). Pour définir une constante correspondant à une durée d’un jour, d’un mois, d’une heure, vous pouvez utiliser la syntaxe '1 day'::interval, '1 month'::interval, '1 hour'::interval.
SELECT
tr.velo_num,
count(*) AS nb_trajets
FROM
trajet tr
WHERE
(tr.end IS NOT NULL AND (tr.start - tr.end) > '1 hour'::interval) OR
(tr.end IS NULL AND (current_timestamp - tr.start) > '1 hour'::interval)
GROUP BY
tr.velo_num ;Pour chaque jour de la semaine, listez les quatre couples (station de départ, station d’arrivée) utilisés par le plus grand nombre de trajets ce jour de la semaine là.
En PostgreSQL, pour extraire le jour de la semaine d’un objet ts de type timestamp, vous pouvez utiliser EXTRACT(DOW FROM ts). Le résultat est un entier entre 0 et 6, 0 pour dimanche, 1 pour lundi, …, 7 pour samedi.
postgres=# SELECT
current_timestamp as maintenant ,
EXTRACT(DOW FROM current_timestamp + '1 day'::interval) as dow_demain ;
maintenant | dow_demain
-------------------------------+------------
2024-12-08 10:55:05.672936+01 | 1WITH R AS
(
SELECT
EXTRACT(DOW FROM tr.start) AS jds,
tr.origin_num, tr.dest_num,
COUNT(*) AS nb_trajets
FROM
trajet tr
GROUP BY
EXTRACT(DOW FROM tr.start),
tr.origin_num,
tr.dest_num
), S AS
(
SELECT
R.*,
RANK() OVER (PARTITION BY R.jds ORDER BY R.nb_trajets DESC) AS rnk
FROM
R
)
SELECT
S.*
FROM
S
WHERE S.rnk <= 4 ;Soit \(\mathcal{A} = \{\texttt{A, B, C, D, E, H, S, T}\}\) un schéma.
Soit Σ = {{A, E} \(⟶\) {H}, {S, T} \(⟶\) {C, E}, {S} \(⟶\) {A}, {B} \(⟶\) {T}}
- Quelle est la fermeture de
{B, S, E}? - Quelles sont les clés de \(\mathcal{A}, \Sigma\) ?
- \(\mathcal{A}, \Sigma\) est elle forme normale de Boyce-Codd ?
- \(\texttt{[B, S, E]}_{\Sigma}^+ = \{ \texttt{B, S, E, T, C, A, H}\} = \{ \texttt{A, B, C, E, H, S, T}\}\)
- Toute clé contient \(\texttt{B, D, S}\) qui est une super-clé \([\texttt{B, D, S}]^+_\Sigma = \{\texttt{B, D, S, A, T, C, E, H}\}\). C’est la seule clé.
- Le schéma n’est pas en FNBC, toutes les DF de Σ ont un déterminant qui n’est pas une super-clé.